/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import POJO.PNhapKho;
import POJO.SoLuongXuat;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author kim
 */
public class PNhapKhoDao {

	public static ArrayList<PNhapKho> layDanhSach() {
		ArrayList<PNhapKho> ds = new ArrayList<PNhapKho>();
		String str = "SELECT * FROM PNhapKho";
		String sql = str;

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				PNhapKho pnk = new PNhapKho();
				pnk.setSoPNK(rs.getInt("SoPNK"));
				pnk.setNgayNhap(rs.getDate("NgayNhap"));
				pnk.setMaNCC(rs.getInt("MaNCC"));
				pnk.setMaNVPhuTrach(rs.getInt("MaNVPhuTrach"));

				ds.add(pnk);
			}

		} catch (Exception ex) {
			Logger.getLogger(PNhapKhoDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static int laySoDong() {
		String sql = "select count(SoPNK) as COU from PNhapKho ";
		KetNoi cn = null;
		ResultSet rs = null;
		int n = 0;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				n = rs.getInt("COU");
			}
		} catch (Exception ex) {
			Logger.getLogger(PNhapKhoDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		return n;
	}

	public static ArrayList<PNhapKho> layDanhSachByNhanVienPhanTrang(int MaNV, int soDong, int trangSo) {
		ArrayList<PNhapKho> ds = new ArrayList<PNhapKho>();
		String sql = "with s as ( select ROW_NUMBER() OVER(ORDER By SoPNK desc) as row ,PNhapKho.* from PNhapKho where PNhapKho.MaNVPhuTrach =" + MaNV
			 + " ) select * from s where row between " + (soDong * trangSo + 1)
			 + " and " + (trangSo * soDong + soDong)
			 + " order by SoPNK asc;";

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				PNhapKho tmp = new PNhapKho();
				tmp.setSoPNK(rs.getInt("SoPNK"));
				tmp.setNgayNhap(rs.getDate("NgayNhap"));
				tmp.setMaNCC(rs.getInt("MaNCC"));
				tmp.setMaNVPhuTrach(rs.getInt("MaNVPhuTrach"));

				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(PNhapKhoDao.class.getName()).log(Level.SEVERE, null, ex);


		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<PNhapKho> layDanhSachPhanTrang(int soDong, int trangSo) {
		ArrayList<PNhapKho> ds = new ArrayList<PNhapKho>();
		String sql = "with s as ( select ROW_NUMBER() OVER(ORDER By SoPNK desc) as row ,PNhapKho.* from PNhapKho "
			 + " ) select * from s where row between " + (soDong * trangSo + 1)
			 + " and " + (trangSo * soDong + soDong)
			 + " order by SoPNK asc;";

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				PNhapKho tmp = new PNhapKho();
				tmp.setSoPNK(rs.getInt("SoPNK"));
				tmp.setNgayNhap(rs.getDate("NgayNhap"));
				tmp.setMaNCC(rs.getInt("MaNCC"));
				tmp.setMaNVPhuTrach(rs.getInt("MaNVPhuTrach"));

				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(PNhapKhoDao.class.getName()).log(Level.SEVERE, null, ex);


		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<PNhapKho> layDanhSachPNK_NCC() {
		ArrayList<PNhapKho> ds = new ArrayList<PNhapKho>();
		String str = "SELECT PNK.*,NCC.* FROM PNhapKho PNK,NhaCungCap NCC where PNK.MaNCC = NCC.MaNCC";
		String sql = str;

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				PNhapKho pnk = new PNhapKho();
				pnk.setSoPNK(rs.getInt("SoPNK"));
				pnk.setNgayNhap(rs.getDate("NgayNhap"));
				pnk.setMaNCC(rs.getInt("MaNCC"));
				pnk.setMaNVPhuTrach(rs.getInt("MaNVPhuTrach"));

				ds.add(pnk);
			}

		} catch (Exception ex) {
			Logger.getLogger(PNhapKhoDao.class.getName()).log(Level.SEVERE, null, ex);


		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static boolean them(PNhapKho tmp) {
		boolean kq = false;
		int thang = tmp.getNgayNhap().getMonth();
		thang += 1;//co 12 thang trong 1 nam //thang dau tien = 0
		String ng = tmp.getNgayNhap().getYear() + "-" + thang + "-" + tmp.getNgayNhap().getDate();
		int so = PNhapKhoDao.laySoPNhapKhoMax();
		so += 1;
		tmp.setSoPNK(so);
		String sql = String.format(
			 "INSERT INTO PNhapKho(SoPNK,NgayNhap,MaNCC,MaNVPhuTrach) VALUES ('%d','%s','%d','%d');", tmp.getSoPNK(), ng, tmp.getMaNCC(), tmp.getMaNVPhuTrach());
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(PNhapKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}
		return kq;
	}

	public static int laySoPNhapKhoMax() {
		String sql = "select pn.SoPNK from PNhapKho pn where pn.SoPNK >= all(select pn1.SoPNK from PNhapKho pn1 ); ";
		KetNoi cn = null;
		ResultSet rs = null;
		int n = 0;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				n = rs.getInt("SoPNK");
			}
		} catch (Exception ex) {
			Logger.getLogger(PNhapKhoDao.class.getName()).log(Level.SEVERE, null, ex);


		} finally {
			cn.dongKetNoi();
		}
		return n;
	}

	public static boolean xoa(int soPNK) {
		boolean kq = false;
		String sql = String.format("DELETE FROM PNhapKho WHERE SoPNK=%d", soPNK);
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(PNhapKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}
		return kq;
	}

	public static boolean capNhat(PNhapKho tmp) {
		boolean kq = false;
		String ng = tmp.getNgayNhap().getYear() + "-" + tmp.getNgayNhap().getMonth() + "-" + tmp.getNgayNhap().getDate();
		String sql = String.format("UPDATE PNhapKho"
			 + "SET"
			 + "NgayNhap = '%s', "
			 + "MaNCC = %d "
			 + "MaNVPhuTrach = %d "
			 + "WHERE SoPNK = %d; ", ng, tmp.getMaNCC(), tmp.getMaNVPhuTrach(), tmp.getSoPNK());
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(PNhapKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}
		return kq;
	}
}
